package com.keli.dt.dao.impl;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.keli.dt.dao.ITravelReportDao;
import com.keli.dt.model.LaneReport;
import com.keli.dt.model.TravelReport;
import com.keli.dt.model.rowmapper.LaneReportRowMapper;
import com.keli.dt.model.rowmapper.TravelReportRowMapper;

public class TravelReportDao extends JdbcDaoSupport implements ITravelReportDao {
	private Logger logger = Logger.getLogger(this.getClass());

	@Override
	public int count() {
		String sql = "SELECT COUNT(*) FROM DT_TRAVEL_REPORT_B";	//  WHERE DDBH = ?
		logger.debug(sql);
		return this.getJdbcTemplate().queryForInt(sql); // , "230601"
	}

	@Override
	public List<TravelReport> getTravelReportListByTime(String from, String to) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.*, TO_CHAR(t.GCSJ, 'YYYY/MM/DD HH24:MI:SS') AS strgcsj, ");
		sql.append("d.DETECT_CLASS, d.SECTIONID ");
		sql.append(" FROM DT_TRAVEL_REPORT_B t, DT_DETECT_DECIVES_B d ");
		sql.append(" WHERE t.GCSJ BETWEEN TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS')");
		sql.append(" AND t.SBBH = d.DETECT_ID");
		
		logger.debug(sql.toString());
		
		return this.getJdbcTemplate().query(sql.toString(), new TravelReportRowMapper(), from, to);
	}

	@Override
	public List<LaneReport> getLaneReportListByTime(String from, String to) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DETECT_CLASS, SBBH, DDBH, CDBH, HPZL, AVG(TO_NUMBER(CLSD, '999.99')) AS SPEED, COUNT(*) AS VOLUME, ");
		sql.append("TO_CHAR(MAX(GCSJ), 'YYYY/MM/DD HH24:MI:SS') AS COLLEC_DATETIME, d.SECTIONID FROM DT_TRAVEL_REPORT_B t, DT_DETECT_DECIVES_B d ");
		sql.append("WHERE t.GCSJ BETWEEN TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS')");
		sql.append(" AND t.SBBH = d.DETECT_ID ");
		sql.append("GROUP BY DETECT_CLASS, SBBH, DDBH, CDBH, HPZL, SECTIONID");
		
		logger.debug(sql.toString());
		
		return this.getJdbcTemplate().query(sql.toString(), new LaneReportRowMapper(), from, to);
	}

	@Override
	public List<TravelReport> findAll() {
		String sql = "SELECT t.*, TO_CHAR(t.GCSJ, 'YYYY/MM/DD HH24:MI:SS') AS strgcsj FROM DT_TRAVEL_REPORT_B t";
		logger.debug(sql);
		return this.getJdbcTemplate().query(sql.toString(), new TravelReportRowMapper());
	}

	@Override
	public List<TravelReport> getTravelReportListWithFilterByTime(String from,
			String to) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.*, TO_CHAR(t.GCSJ, 'YYYY/MM/DD HH24:MI:SS') AS strgcsj, ");
		sql.append("d.DETECT_CLASS, d.SECTIONID ");
		sql.append(" FROM DT_TRAVEL_REPORT_B t, DT_DETECT_DECIVES_B d ");
		sql.append(" WHERE TO_NUMBER(CLSD, '999.99') > 0  ");
		sql.append(" AND t.GCSJ BETWEEN TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS')");
		sql.append(" AND t.SBBH = d.DETECT_ID");
		
		logger.debug(sql.toString());
		
		return this.getJdbcTemplate().query(sql.toString(), new TravelReportRowMapper(), from, to);
	}
}
